<?php
	/* data utility
	 * 
	 * created by : michael.ocana
	 * on : march 2010
	 * 
	 */
	class DataUtility {
		
		/* get all organisms with attributes including prizes and sizes
		 * 
		 * parameters : $params = array("condition" => "moid = '000003'");
		 * 				$key = "by_organization"
		 */
		public function find_Organisms ($organization_id, $key = "", $conditions = array() ) {			
			Zend_Loader::loadClass("Organism");			
			Zend_Loader::loadClass("OrganismGroup");
			Zend_Loader::loadClass("OrganismType");	
			Zend_Loader::loadClass("OrganismAttribute");
			Zend_Loader::loadClass("Size");
			Zend_Loader::loadClass("Inventory");
			Zend_Loader::loadClass("OrganizationOrganism");
								
			Zend_Loader::loadClass("QueryCreator");

			$db = DBCon::instance();	
			
			if ($key == "by_organization") {
				$organisms = $this->get_organization_organisms($organization_id, $conditions);
			} else if ($key == "by_user_type") {
				$organisms = $this->get_organisms_per_user_type($conditions['user_type'], $conditions);
			}
			
			$o_organisms = array();
			
			foreach ($organisms as $key => $value) {
				$qry_attributes = new QueryCreator();	

				$qry_attributes->addSelect("oo.*, inv.currency_mark, sz.size_code, sz.size, oa.gender_code, inv.stock_amount, inv.inventory_id");			
				$qry_attributes->addFrom(OrganizationOrganism::_tablename." oo");
				$qry_attributes->addJoin("left join ".OrganismAttribute::_tablename." oa on oa.organism_attribute_id = oo.organism_attribute_id AND oa.organism_id = oo.organism_id");
				$qry_attributes->addJoin("left join ".Inventory::_tablename." inv on inv.organism_attribute_id = oa.organism_attribute_id AND inv.org_id = oo.org_id");
				$qry_attributes->addJoin("left join ".Size::_tablename." sz on sz.size_code = oa.size_code");
				$qry_attributes->addWhere("oo.organism_id = ".$value['id']);
				$qry_attributes->addWhere("oo.org_id = ".$value['org_id']);	
				$qry_attributes->addWhere("inv.inventory_id > 0");	
				$qry_attributes->addOrderBy("sz.display_order");	
				
				$o_organisms[] = array (
						"id" => $value['id'],
						"org_id" => $value['org_id'],
			            "organism_id" => $value['organism_id'],
						"moid" => $value['moid'],
						"name" => $value['name'],
						"image" => $value['image'],
			            "scientific_name" => $value['scientific_name'],
						"description" => $value['description'],
						"sale_unit" => $value['sale_unit'],
						"organism_group" => $value['organism_group'],
						"organism_group_description" => $value['organism_group_description'],
						"organism_type" => $value['organism_type'],
						"organism_type_description" => $value['organism_type_description'],
    					"organism_group_id" => $value['organism_group_id'],
						"picture_document_id" => $value['picture_document_id'],
						"default_inventory_cost" => $value['default_inventory_cost'],
						"default_base_price_sale" => $value['default_base_price_sale'],
						"currency_mark" => $value['currency_mark'],
						"country_code" => $value['country_code'],
						"province" => $value['province'],
						"mac_certified" => $value['mac_certified'],
						"unsuitable_species" => $value['unsuitable_species'],
						"date_created" => $value['date_created'],
						"date_updated" => $value['date_updated'],
						"created_by" => $value['created_by'],
						"updated_by" => $value['updated_by'],
						"unsuitable_species" => $value['unsuitable_species'],
						"attributes" => $db->executeQuery($qry_attributes->createSQL())											
				);	
	
			}

			//print_r($o_organisms);
			return $o_organisms;
			
		}
		
		/* get all organism list no attributes
		 * 
		 */
		public function get_organization_organisms($organization_id, $conditions = array()) {
			Zend_Loader::loadClass("Organism");			
			Zend_Loader::loadClass("OrganismGroup");
			Zend_Loader::loadClass("OrganismType");	
			Zend_Loader::loadClass("OrganizationOrganism");
								
			Zend_Loader::loadClass("QueryCreator");

			$db = DBCon::instance();
			
			$query = new QueryCreator();
			$query->addSelect("o.*, o.moid as moid, o.name as name, o.scientific_name as scientific_name, og.organism_group as organism_group");
			$query->addSelect("og.description as organism_group_description, oo.org_id as org_id");
			$query->addSelect("ot.organism_type as organism_type, ot.description as organism_type_description");
			$query->addSelect("replace(o.scientific_name,' ','-') as image");
			$query->addFrom(OrganizationOrganism::_tablename." oo");
			$query->addJoin("left join ".Organism::_tablename." o on oo.organism_id = o.organism_id ");
			$query->addJoin("left join ".OrganismGroup::_tablename." og on o.organism_group_id = og.organism_group_id");
			$query->addJoin("left join ".OrganismType::_tablename." ot on og.organism_type = ot.organism_type");
			$query->addWhere("oo.org_id = ".$organization_id);

			if (count($conditions) > 0 && strlen($conditions["condition"]) > 0) {
				$query->addWhere($conditions["condition"]);
			}
			
			$query->addGroupBy("oo.organism_id");

			$results = $db->executeQuery($query->createSQL());	

			return $results;
									
		}
		
		/* 
		 * $user_type => exporter, importer, fma, etc
		 */
		public function get_organisms_per_user_type($user_type, $conditions = array()) {
			Zend_Loader::loadClass("Organism");			
			Zend_Loader::loadClass("OrganismGroup");
			Zend_Loader::loadClass("OrganismType");	
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("Organization");
								
			Zend_Loader::loadClass("QueryCreator");

			$db = DBCon::instance();
			
			$query = new QueryCreator();		
			$query->addSelect("o.*, o.moid as moid, o.name as name, o.scientific_name as scientific_name, og.organism_group as organism_group");
			$query->addSelect("og.description as organism_group_description, org.org_type as org_type");
			$query->addSelect("ot.organism_type as organism_type, ot.description as organism_type_description");
			$query->addSelect("replace(o.scientific_name,' ','-') as image, oo.org_id as org_id");
			$query->addFrom(OrganizationOrganism::_tablename." oo");
			$query->addJoin("left join ".Organism::_tablename." o on oo.organism_id = o.organism_id ");
			$query->addJoin("left join ".OrganismGroup::_tablename." og on o.organism_group_id = og.organism_group_id");
			$query->addJoin("left join ".OrganismType::_tablename." ot on og.organism_type = ot.organism_type");
			$query->addJoin("left join ".Organization::_tablename." org on org.org_id = oo.org_id");
			$query->addWhere("org.org_type = '".$user_type."'");	
					//print $conditions["condition"];
			if (count($conditions) > 0) {
				if (strlen($conditions["condition"]) > 0) {
				 $query->addWhere($conditions["condition"]);
				}
			}
			
			$query->addGroupBy("oo.organism_id");
			//print $query->createSQL();
			//die();
			$results = $db->executeQuery($query->createSQL());	

			return $results;				
		}
		
		/*
		 * Retrieve the tank inventory
		 *   table aliases = oo, o, og, oa, sz, tc, inv, tnk
		 */
		public function get_organisms_inventory($organization_id, $conditions = array()) {
			Zend_Loader::loadClass("OrganismGroup");
			Zend_Loader::loadClass("Organism");
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("OrganismAttribute");
			Zend_Loader::loadClass("Size");
			Zend_Loader::loadClass("Tac");			
			Zend_Loader::loadClass("Inventory");
			Zend_Loader::loadClass("Tank");	
														
			Zend_Loader::loadClass("QueryCreator");

			$db = DBCon::instance();
			
			$org_inventories = array();
			
			$query = new QueryCreator();						
					
			$query->addSelect("oo.org_organism_id, oo.org_id, oo.organism_id, oo.ordering_status, oo.inventory_cost, oo.last_buy, oo.last_sale");
			$query->addSelect("o.organism_id, o.moid, o.name, o.scientific_name, o.description, o.country_code, o.province, o.unsuitable_species");
			$query->addSelect("replace(o.scientific_name,' ','-') as image");
			$query->addSelect("og.organism_group_id, og.organism_group, og.organism_type, og.description as og_description");
			$query->addSelect("oa.organism_attribute_id");
			$query->addSelect("sz.size_code, sz.size");
			$query->addSelect("tc.fishing_area_id, tc.limit_quantity, tc.date_start, tc.date_expire, tc.current");
			$query->addFrom(OrganizationOrganism::_tablename." oo");
			$query->addJoin("LEFT JOIN ".Organism::_tablename." o on o.organism_id = oo.organism_id");
			$query->addJoin("LEFT JOIN ".OrganismGroup::_tablename." og on og.organism_group_id = o.organism_group_id");			
			$query->addJoin("LEFT JOIN ".OrganismAttribute::_tablename." oa on oo.organism_attribute_id = oa.organism_attribute_id AND oa.organism_id = oo.organism_id");
			$query->addJoin("LEFT JOIN ".Size::_tablename." sz on sz.size_code = oa.size_code");	
			$query->addJoin("RIGHT JOIN ".Tac::_tablename." tc on tc.organism_id = o.organism_id");				
			
			$query->addWhere("oo.org_id = ".$organization_id);
			
			if (count($conditions) > 0) {
				if (strlen($conditions["condition"]) > 0) {
				 $query->addWhere($conditions["condition"]);
				}
			}
			//print $query->createSQL();
			$results = $db->executeQuery($query->createSQL());	
			
			foreach ($results as $key => $result) {
				$qryInventory = new QueryCreator();	
				$qryInventory->addSelect("inv.inventory_id, inv.quantity_on_hand, inv.quantity_on_shipment");
				$qryInventory->addSelect("inv.total_quantity_received, inv.total_quantity_withdrawn, inv.stock_amount, inv.currency_mark");
				$qryInventory->addSelect("tnk.name, tnk.description, tnk.dimensions, tnk.date_installed");
				
				$qryInventory->addFrom(Inventory::_tablename." inv");
				$qryInventory->addJoin("LEFT JOIN ".Tank::_tablename." tnk on tnk.tank_id = inv.tank_id AND tnk.org_id = inv.org_id");	
								
				$qryInventory->addWhere("inv.org_organism_id = ".$result['org_organism_id']);
				$qryInventory->addWhere("inv.organism_attribute_id = ".$result['organism_attribute_id']);
			
				if (count($conditions) > 0) {
					if (strlen($conditions["tank_query"]) > 0) {
						$qryInventory->addWhere($conditions["tank_query"]);
					}
				}
			
				$inv_results = $db->executeQuery($qryInventory->createSQL());	
				$org_inventories[$key] = $result;
				
				$org_inventories[$key]['inventory'] = $inv_results;

			}						


			print_r($org_inventories);die();

			return $org_inventories;						
		}
		
		/*
		 * retrieved all available attributes/sizes for the specified organism and include the inventory per tank, official price, etc
		 *    $options => array("conditions" => " alias.field_name = value")
		 */
		public function get_organism_available_attributes($organization_id, $organism_id, $options=array()) {
			Zend_Loader::loadClass("OrganismGroup");
			Zend_Loader::loadClass("Organism");
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("OrganismAttribute");
			Zend_Loader::loadClass("Size");
														
			Zend_Loader::loadClass("QueryCreator");

			$db = DBCon::instance();
			
			$query = new QueryCreator();

			$query->addSelect("oo.org_id, oo.organism_id, og.organism_group, og.organism_type, o.moid, o.name");
			$query->addSelect("o.scientific_name, oa.size_code, oa.gender_code, sz.size, oa.organism_attribute_id, oo.org_organism_id");
			$query->addFrom(OrganizationOrganism::_tablename." oo");
			$query->addJoin("LEFT JOIN ".OrganismAttribute::_tablename." oa ON oo.organism_id = oa.organism_id AND oo.organism_attribute_id = oa.organism_attribute_id");
			$query->addJoin("LEFT JOIN ".Size::_tablename." sz ON on oa.size_code = sz.size_code");
			$query->addJoin("LEFT JOIN ".Organism::_tablename." o ON oo.organism_id = o.organism_id");
			$query->addJoin("LEFT JOIN ".OrganismGroup::_tablename." og ON og.organism_group_id = o.organism_group_id");
			$query->addWhere("oo.org_id = ".$organization_id);
			$query->addWhere("oo.organism_id = ".$organism_id);
			
			if (count($options) > 0) {
				$query->addWhere($options['conditions']);
			}
			$query->addOrderBy("sz.display_order");
			
			$results = $db->executeQuery($query->createSQL());	
			
			$result_array = array();
			
			foreach($results as $key => $row) {
				$size_inventory = $this->get_organism_inventory_from_tanks($organization_id, $row['org_organism_id'], $row['organism_attribute_id']);
				
				$result_array[] = array("org_id" => $row['org_id'],
										"organism_id" => $row['organism_id'],
										"organism_group" => $row['organism_group'],
										"organism_type" => $row['organism_type'],
										"moid" => $row['moid'],
										"name" => $row['name'],
										"scientific_name" => $row['scientific_name'],
										"size_code" => $row['size_code'],
										"gender_code" => $row['gender_code'],
										"size" => $row['size'],
										"organism_attribute_id" => $row['organism_attribute_id'],
										"org_organism_id" => $row['org_organism_id'],
										"inventory" => $size_inventory
				); 
			}
			
			return $result_array;

		}
		
		function get_organism_inventory_from_tanks($organization_id, $org_organism_id, $organism_attribute_id){
			Zend_Loader::loadClass("Inventory");
			Zend_Loader::loadClass("Tank");
			
			Zend_Loader::loadClass("QueryCreator");

			$db = DBCon::instance();
			
			$query = new QueryCreator();
			
			$query->addSelect("i.inventory_id, t.system_name, t.name, t.description, i.tank_id, i.quantity_on_hand, i.quantity_on_shipment");
			$query->addSelect("i.total_quantity_received, i.total_quantity_withdrawn, i.stock_amount, i.currency_mark");			
			$query->addFrom(Inventory::_tablename." i");
			$query->addJoin(Tank::_tablename." t on i.tank_id = t.tank_id");
			$query->addWhere("i.org_id = ".$organization_id);
			$query->addWhere("i.org_organism_id = ".$org_organism_id);
			$query->addWhere("i.organism_attribute_id = ".$organism_attribute_id);
			$query->addOrderBy("t.name");
			
			$results = $db->executeQuery($query->createSQL());	
			
			return $results;
		}
		
	}
?>